﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MMS.Model;
using System.Data.SqlClient;
using System.Data.Common;

namespace MMS.DAL
{
    public class OrderService : BaseDAL
    {

        private DBHelper db = DBHelper.GetDBHelper();
        /// <summary>
        /// 添加订单
        /// </summary>
        /// <param name="list">购买的商品集合</param>
        /// <param name="totalMoney">商品总金额</param>
        /// <param name="adminId">员工编号</param>
        /// <returns>如果添加成功返回true，否则返回false</returns>
        public bool InsertOrder(List<BuyGoodsInfo> list, double totalMoney, int adminId)
        {
            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=mmsdb;Integrated Security=True");
            try
            {
                //TODO:后续使用事务完善程序流程
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                //1.插入订单
                string sql = string.Format("insert into OrderInfo (OrderMoney,AdminId) values ({0},{1})", totalMoney, adminId);
                cmd.CommandText = sql;
                int count = cmd.ExecuteNonQuery();//执行插入语句获取受影响的行数
                if (count == 0)
                {//判断是否插入数据成功
                    return false;
                }
                //2.查询生成的订单编号
                sql = "select @@identity";
                cmd.CommandText = sql; //查询最近一次插入语句生成的标识列的值
                object orderId = cmd.ExecuteScalar();
                if (orderId == null)
                {//没有查询到 则表示生成订单是失败的
                    return false;
                }
                //3.利用生成的订单编号添加商品数据
                foreach (BuyGoodsInfo item in list)
                {
                    sql = string.Format(
                        "insert into OrderGoods values ({0},'{1}',{2},{3},{4},{5})",
                        orderId, item.GoodsCode, item.SalePrice, item.BuyCount, item.XiaoJi, item.Coupon);
                    cmd.CommandText = sql;
                    //默认认为都会执行成功，暂时不要考虑添加影响行数
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }

            return true;
        }

        public List<OrderGoods> SelectTop10BuyCountGoods(DateTime start, DateTime end)
        {
            string sql = string.Format(@"select 
	top 10
	og.GoodsCode,g.GoodsName,SUM(BuyCount) as BuyCount,SUM(XiaoJi) as GoodsPrice
from OrderGoods og, OrderInfo o,GoodsInfo g
where 
	o.OrderId = og.OrderId 
and og.GoodsCode = g.GoodsCode
and OrderTime between '{0} 00:00:00' and '{1} 23:59:59'
group by og.GoodsCode,g.GoodsName
order by BuyCount desc", start.ToShortDateString(), end.ToShortDateString());
            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=mmsdb;Integrated Security=True");
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            List<OrderGoods> list = new List<OrderGoods>();
            while (dr.Read())
            {
                OrderGoods og = new OrderGoods();
                og.GoodsCode = dr["GoodsCode"].ToString();
                og.GoodsName = dr["GoodsName"].ToString();
                og.BuyCount = Convert.ToInt32(dr["BuyCount"]);
                og.GoodsPrice = Convert.ToDouble(dr["GoodsPrice"]);
                list.Add(og);
            }
            dr.Close();
            db.Close();

            return list;
        }

        public List<GoodsOrderDetail> SelectGoodsOrderDetail(string goodsCode, DateTime start, DateTime end)
        {
            string sql = string.Format(@"select
	*,(SalePrice - OriginPrice)*BuyCount as Profit
from 
	OrderInfo o,GoodsInfo g,OrderGoods og
where
	o.OrderId = og.OrderId
and g.GoodsCode = og.GoodsCode
and g.GoodsCode = '{0}'
and OrderTime between '{1} 00:00:00' and '{2} 23:59:59'
", goodsCode, start.ToShortDateString(), end.ToShortDateString());
            return GetListBySQL<GoodsOrderDetail>(sql);
        }
    }
}
